global.ReadableStream = require('stream').Readable;
const express = require('express')
const cors = require('cors')
const mysql = require('mysql2')
const crypto = require('crypto')
const cheerio = require('cheerio')
const multer = require('multer');
const formidable = require('formidable')
const path = require('path')
const fs = require('fs')
const tool = require('./tool')
const xlsx = require('xlsx')
const http = require('http')
const axios = require('axios')
const QRCode = require('qrcode')

// const connectionConfig = {
//     host: 'localhost',
//     user: 'root',
//     password: 'root',
//     database: 'fy',
//     port: 3306
// }

const connectionConfig = {
    host: '110.42.246.177',
    user: 'root',
    password: 'Catiouszty2004_',
    database: 'fy',
    port: 3306,
    charset: 'utf8mb4'
}

const config = {
    port: 1000,
    prefix: '/api'
}

const connection = mysql.createConnection(connectionConfig);

console.clear()

//连接数据库
function connectDatabase() {
    return new Promise((resolve, reject) => {
        connection.connect(err => {
            return resolve(err == null ? 'OK' : err.toString())
        })
    })
}

const getInsertIntoSQLString = (string) => {
    string = string.split('')
    let re = ''
    for (let i = 0; i < string.length; i++) {
        if (string[i] == "'") string[i] = '`'
        re += string[i]
    }
    return re
}
//执行SQL
function execSQL(SQL) {
    return new Promise((resolve, reject) => {
        connection.query(SQL, (error, results, fields) => {
            if (error) {
                console.log(error)
                connection.query(`insert into log_sql(\`sql\`, status, results, time) values('${getInsertIntoSQLString(SQL)}',0,${error.toString()},'${(new Date()).toString()}')`)
                return reject()
            }
            connection.query(`insert into log_sql(\`sql\`, status, results, time) values('${getInsertIntoSQLString(SQL)}',1,${JSON.stringify(results)},'${(new Date()).toString()}')`, (error1, results1, field1) => {
                console.log(error1)
            })
            return resolve(results)
        })
    })
}

//抛出错误
function throwNewError(error) {
    throw new Error(error)
}

//数据库方法
const dataBaseMethods = {
    async exe(sql) {
        console.log(sql)
        return await execSQL(sql)
    },
    async getTableColumns(tableName) {
        try {
            return await tool.array.format(await execSQL(`describe \`${tableName}\`;`), (e) => {
                return {
                    name: e.Field,
                    type: e.Type
                }
            })
        } catch (error) {
            throwNewError(`不存在表${tableName}`)
        }
    },
    async genNewID(tableName) {
        let re = await execSQL(`select id from \`${tableName}\` order by id asc`)
        let index = 1
        while (1) {
            if (re[index - 1] == undefined) return index
            if (re[index - 1].id != index) return index
            index++
        }
    },
    async insertInto(tableName, values = {}) {
        let re = await this.getTableColumns(tableName)
        let sql = `insert into \`${tableName}\` values(`
        values.id = await this.genNewID(tableName)
        values.created_at = (new Date()).toString()
        values.updated_at = (new Date()).toString()
        for (let i = 0; i < re.length; i++) {
            let value
            if (values[re[i].name] == undefined) {
                value = (re[i].type.indexOf('int') != -1) ? 0 : ''
            } else {
                value = values[re[i].name]
            }
            if (!((typeof (value) == 'number' && re[i].type.indexOf('int') != -1) || (typeof (value) == 'string' && re[i].type == 'longtext'))) {
                throwNewError(`${re[i].name}的值的类型应为${re[i].type}，而不是${typeof (value)}`)
            }
            if (i) sql += ','
            if (typeof (value) == 'number') {
                sql += value.toString()
            } else {
                sql += "'" + value.toString() + "'"
            }
        }
        sql += ');'
        await this.exe(sql)
        return values.id
    },
    async deleteById(tableName, id) {
        return await this.exe(`delete from \`${tableName}\` where id = ${id}`)
    },
    async updateById(tableName, id, values = {}) {
        let re = await this.getTableColumns(tableName)
        let sql = `update ${tableName} set `
        delete values.id
        delete values.created_at
        values.updated_at = (new Date()).toString()
        let c = 0
        for (let i = 0; i < re.length; i++) {
            if (values[re[i].name] == undefined) continue
            let value = values[re[i].name]
            if (!((typeof (value) == 'number' && re[i].type.indexOf('int') != -1) || (typeof (value) == 'string' && re[i].type == 'longtext'))) {
                throwNewError(`${re[i].name}的值的类型应为${re[i].type}，而不是${typeof (value)}`)
            }
            if (c) sql += ', '
            sql += "`" + re[i].name + "` = "
            if (re[i].type.indexOf('int') != -1) {
                sql += value.toString()
            } else {
                sql += "'" + value.toString() + "'"
            }
            c++
        }
        sql += ` where id = ${id};`
        return await this.exe(sql)
    },
    async select(sql, format = {}) {
        if (sql.slice(0, 6) != 'select') throwNewError('SQL语句不是select语句')
        let re = await this.exe(sql)
        format.created_at = 'date'
        format.updated_at = 'date'
        return tool.array.format(re, async (item) => {
            let t = await tool.object.for(item, (k, v) => {
                if (item[k] != undefined) {
                    if (format[k] == 'date') {
                        return new Date(item[k])
                    }
                    if (format[k] == 'number') {
                        return parseFloat(item[k])
                    }
                    try {
                        return JSON.parse(item[k])
                    } catch (error) {

                    }
                    return item[k]
                } else {
                    return null
                }
            })
            return t
        })
    },
}

//系统设置
const settings = {
    parseValue(type, value) {
        if (type == 'string') {
            return value.toString()
        }
        if (type == 'number') {
            return parseFloat(value)
        }
        if (type == 'date') {
            return new Date(value)
        }
        if (type == 'object') {
            return JSON.parse(value)
        }
        if (type == 'boolean') {
            return value.toString() == 'true' ? true : false
        }
        return value
    },
    async getItem(key) {
        let re = await dataBaseMethods.select("select * from settings where `key` = '" + key.toString() + "'")
        if (re.length) {
            return this.parseValue(tool.JSON.parse(re[0].value).type, tool.JSON.parse(re[0].value).value)
        } else {
            return null
        }
    },
    async getItemInfo(key) {
        let re = await dataBaseMethods.select("select * from settings where `key` = '" + key.toString() + "'")
        if (re.length) {
            let t = re[0]
            t.valueParsed = this.parseValue(tool.JSON.parse(re[0].value).type, tool.JSON.parse(re[0].value).value)
            return t
        } else {
            return undefined
        }
    },
    async setItem(key, value, explain = undefined) {
        if (!key) {
            throw new Error(`未指定key`)
        }
        let t
        if ((await this.getItem(key)) != null) {
            t = await this.getItem(key)
            await this.removeItem(key)
        }
        await dataBaseMethods.insertInto('settings', {
            key: key,
            value: JSON.stringify({
                type: typeof (value),
                value: value
            }),
            explain: explain == undefined ? t.explain : explain
        })
    },
    async removeItem(key) {
        await execSQL(`remove from settings where \`key\` = ${key}`)
    },
    list: [
        {
            key: 'allowRegister_heritageInheritor',
            default: true,
            explain: '允许注册 - 非遗传承人'
        },
        {
            key: 'allowRegister_businessUser',
            default: true,
            explain: '允许注册 - 企业用户'
        },
        {
            key: 'allowRegister_platformAdmin',
            default: true,
            explain: '允许注册 - 平台管理员'
        },
        {
            key: 'allowRegister_consumer',
            default: true,
            explain: '允许注册 - 消费者'
        },
    ],
    async init() {
        let currentList = await dataBaseMethods.select(`select * from settings`)
        for (let i = 0; i < currentList.length; i++) {
            currentList[i].valueParsed = this.parseValue(tool.JSON.parse(currentList[i].value).type, tool.JSON.parse(currentList[i].value).value)
        }
        const exist = (key) => {
            for (let i = 0; i < currentList.length; i++) {
                if (currentList[i].key == key) {
                    return true
                }
            }
            return false
        }
        for (let i = 0; i < this.list.length; i++) {
            if (exist(this.list[i].key) == false) {
                await this.setItem(this.list[i].key, this.list[i].default, this.list[i].explain)
            }
        }
    }
}
//初始化系统设置
settings.init()

//用户方法
const user = {
    async add(data) {
        if ((await dataBaseMethods.select(`select * from user where username = '${data.username}'`)).length) {
            throwNewError('用户名已存在')
        }
        let attrs = await dataBaseMethods.getTableColumns('users')
        for (let i = 0; i < attrs.length; i++) {
            if (attrs[i].name == 'id') continue
            if (attrs[i].name == 'created_at') continue
            if (attrs[i].name == 'updated_at') continue
            if (attrs[i].name == 'deleted') continue
            if (data[attrs[i].name] == undefined) {
                throwNewError(`用户信息缺少${attrs[i]}`)
            }
        }
        return await dataBaseMethods.insertInto('user', data)
    },
    async delete(id) {
        await dataBaseMethods.updateById('users', id, {
            deleted: 1
        })
    },
    getInfo: {
        all: {
            async byId(id) {
                let re = await dataBaseMethods.select(`select * from users where id = ${id}`)
                if (re.length) {
                    return re[0]
                } else {
                    return undefined
                }
            },
            async byUsername(username) {
                let re = await dataBaseMethods.select(`select * from users where username = '${username}'`)
                if (re.length) {
                    return re[0]
                } else {
                    return undefined
                }
            }
        },
        active: {
            async byId(id) {
                let re = await dataBaseMethods.select(`select * from users where id = ${id} and deleted = 0`)
                if (re.length) {
                    return re[0]
                } else {
                    return undefined
                }
            },
            async byUsername(username) {
                let re = await dataBaseMethods.select(`select * from users where username = '${username}' and deleted = 0`)
                if (re.length) {
                    return re[0]
                } else {
                    return undefined
                }
            }
        }
    },
    async update(id, data) {
        await dataBaseMethods.updateById('users', id, data)
    }
}

async function main() {
    try {
        console.clear()
        let re = await connectDatabase()
        if (re != 'OK') {
            console.log('fy server 未能正常启动，原因：' + re)
            process.exit(0)
        }
        const app = express();
        app.use(express.json())
        app.use(cors())
        const storage = multer.diskStorage({
            destination: 'files/',
            filename: function (req, file, cb) {
                const uniqueSuffix = Date.now() + '-' + Math.round(Math.random() * 1E9);
                const fileExtension = path.extname(file.originalname);
                cb(null, file.fieldname + '-' + uniqueSuffix + fileExtension);
            }
        })
        const upload = multer({ storage: storage })
        let parsedPorts = []
        const parseAllPorts = (o, currentName = '') => {
            tool.object.for(o, (k, v) => {
                if (v.func == undefined) {
                    parseAllPorts(v, currentName + '/' + k)
                } else {
                    let c = {
                        api: currentName + '/' + k,
                        ...v
                    }
                    parsedPorts.push(c)
                    app[c.method == 'get' ? 'get' : 'post'](config.prefix + c.api, async (req, res) => {
                        let data
                        try {
                            console.log(req.headers)
                            data = decodeURIComponent(req.body.data)
                        } catch (error) {
                            console.log(error)
                        }
                        try {
                            let re = await c.func(data)
                            res.send({
                                status: 1,
                                content: 'success',
                                data: re
                            })
                            return
                        } catch (error) {
                            res.send({
                                status: 0,
                                content: tool.string.delete.bySubString(error.toString(), 'Error:')
                            })
                            return
                        }
                    })
                }
            })
        }
        parseAllPorts(ports)
        app.post(config.prefix + '/file/upload', upload.single('file'), async (req, res) => {
            if (!req.file) {
                res.status(400).json({ error: '上传失败' });


                clog('上传文件', undefined, '', '失败', data)
            } else {
                res.json({
                    status: 1,
                    content: '上传成功',
                    results: req.file.filename
                })


                clog('上传文件', undefined, req.file.filename, '成功', data)
            }
        })
        app.get(config.prefix + '/file/download/:filename', async (req, res) => {
            const filename = req.params.filename;
            if (filename.indexOf('%b64%') != -1) {
                let re = await execSQL("select data from file where name = '" + filename + "'")
                if (re.length == 0) {
                    res.send('')
                    return
                }
                re = re[0].data
                res.setHeader('Content-Type', `image/jpeg`);
                res.send(re)
                return
            }
            const filePath = path.join('files', filename);
            if (!fs.existsSync(filePath)) {
                return res.status(404).json({
                    status: 0,
                    content: '无此文件'
                });
            }
            res.setHeader('Content-Disposition', `filename=${filename}`);
            const fileStream = fs.createReadStream(filePath);
            fileStream.pipe(res);
        })
        app.use(express.json({ limit: '50mb' }))
        app.use(express.urlencoded({ extended: true, limit: '50mb' }))
        app.listen(config.port, async () => {
            console.log('\x1b[32mfy Server Started.\x1b[37m')
            console.log('port:' + config.port.toString())
        })

        // await dataBaseMethods.insertInto('contents', {
        //     id: 1,
        //     type: '123',
        //     title: '11',
        //     created_at: '121'
        // })

        // await dataBaseMethods.insertInto('users', {
        //     id: 1,
        //     type: '123',
        //     title: '11',
        //     created_at: '121'
        // })

        // await dataBaseMethods.updateById('contents', 1, {
        //     id: 1,
        //     type: '123',
        //     title: '11',
        //     created_at: '121'
        // })
    } catch (error) {
        console.log('main函数里的错误：', error)
    }
    console.log(await settings.getItem('allowRegister_heritageInheritor'))
}

main()

//定义接口
const ports = {
    user: {
        login: {
            exp: '用户登录',
            method: 'get',
            async func(data) {
                console.log(345, data)
                return 1
            },
        },
        admin: {
            login: {
                exp: '管理员登录',
                async func(data) {

                }
            }
        }
    }
}